/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package amd.database;

import Classes.Permut;

import amd.core.data.ListDocWeight;
import java.util.ArrayList;

/**
 *
 * @author Latifah Dwi Nurachma
 */
public class DbTermDocument {
/*SELECT  docterm.ID_DOC, document.TITLE, docterm.bobot
FROM document INNER JOIN docterm ON document.ID_DOC = docterm.ID_DOC
WHERE docterm.bobot > 0.0 AND ( docterm.TERM ='model'or docterm.TERM = 'police')
ORDER BY docterm.bobot ASC;
*/
    public static ArrayList<ArrayList<String>> getDocByTermContent(ConnectDB conn, String[] terms, float treshold) throws QueryNoResultException {
        ArrayList<ArrayList<String>> listDoc = new ArrayList<ArrayList<String>>();
        Permut p=new Permut();
        ArrayList<ArrayList<String>> permTerms=new ArrayList<ArrayList<String>>();
        permTerms=p.generate(terms.length);
        String sql=new String();
        int loop=0;
        ArrayList<ArrayList<Object>> obj = new ArrayList<ArrayList<Object>>();
        System.out.println("Ukuran permTerms="+permTerms.size());
        while(loop<permTerms.size()) {
            ArrayList<String> pT= permTerms.get(loop);
            System.out.println("Ukuran pT="+pT.size());
            if(pT.size()>1) {
                String trms[]=new String[pT.size()];
                System.out.println("Ukuran trms="+trms.length);
                for(int ii=0; ii<trms.length; ii++) {
                    System.out.println("pT.get("+ii+")="+pT.get(ii));
                    trms[ii]=terms[Integer.parseInt(pT.get(ii))-1];
                    System.out.println("trms["+ii+"]="+trms[ii]);
                }
                sql ="SELECT d.id_doc,d.title,t1.bobot"+
                            "\nFROM document d";
                int i=0;
                while(i<trms.length){
                    sql = sql + ", docterm t"+String.valueOf(i+1);
                    i++;
                }
                sql=sql+"\nwhere ";
                i=0;
                while(i<trms.length){
                    if(i>0)
                        sql=sql+" and\n\t";
                    sql=sql+" t"+String.valueOf(i+1)+".bobot >= "+treshold;
                    sql=sql+" and d.id_doc=t"+String.valueOf(i+1)+".id_doc";
                    i++;
                }
                i=0;
                while(i<trms.length){
                    sql=sql+"\n\tand t"+String.valueOf(i+1)+".term =\'";
                    sql = sql + trms[i].trim() + "\'";
                    i++;
                }
                sql=sql+"\norder by bobot desc";
                System.out.println("Sebelum Eksekusi database, sql=");
                System.out.println(sql);
                obj = conn.getAllDatas(sql);
                i=0;
                while(i<obj.size()){
                    System.out.println(obj.get(i).get(0).toString()+obj.get(i).get(1).toString());
                    ArrayList<String> tmp = new ArrayList<String>();
                    tmp.add(obj.get(i).get(0).toString());
                    tmp.add(obj.get(i).get(1).toString());
                    listDoc.add(tmp);
                    i++;
                }
            }
            loop++;
        }
        

        sql ="SELECT d.id_doc,d.title,t.bobot"+
                    "\nFROM document d, docterm t"+
                    "\nwhere d.id_doc=t.id_doc and t.bobot>"+treshold+" and (t.term=\'";
        int i=0;
        while(i<terms.length){
            if(i>0){
                sql = sql + " or t.term = \'";
            }
            sql = sql + terms[i].trim() + "\'";
            i++;
        }
        sql=sql+")\norder by bobot desc";

        obj = conn.getAllDatas(sql);
        System.out.println("...SETELAH conn.getALLDatas(sql)...");
        System.out.println("obj.size="+obj.size());
        i=0;
        while(i<obj.size()){
            System.out.println(obj.get(i).get(0).toString()+obj.get(i).get(1).toString());
            ArrayList<String> tmp = new ArrayList<String>();
            tmp.add(obj.get(i).get(0).toString());
            tmp.add(obj.get(i).get(1).toString());
            if(!listDoc.contains(tmp))
                listDoc.add(tmp);
            i++;
        }
        System.out.println("...SETELAH while(i="+i+")...");
        return listDoc;
    }

    public static ArrayList<ArrayList<Object>> getDocFrequencyByTerm(ConnectDB conn, String termContent) throws QueryNoResultException {
        String sql = "SELECT id_doc, freq from docterm WHERE term = \'" + termContent+"\'";
        ArrayList<ArrayList<Object>> obj = conn.getAllDatas(sql);
        return obj;
    }

    public static ListDocWeight getIdDocSumWeightByTermContent(ConnectDB conn, String[] terms) throws QueryNoResultException {
        //create sql query statement... OK...
        String sql = "SELECT DISTINCT(id_doc), SUM(bobot) FROM docterm WHERE term IN(\'";
        int i=0;
        while(i<terms.length){
            if(i>0){
                sql = sql + ", \'";
            }
            sql = sql + terms[i] + "\'";
            i++;
        }
        sql = sql + ") GROUP BY id_doc ORDER BY COUNT(term)  DESC, SUM(bobot) desc;";
        
        ArrayList<ArrayList<Object>> tempObject= conn.getAllDatas(sql);
        i=0;
        ListDocWeight listDoc = new ListDocWeight();
        while(i<tempObject.get(0).size()){
            listDoc.add(tempObject.get(0).get(i).toString(), Float.valueOf(tempObject.get(1).get(i).toString()));
            i++;
        }
        return listDoc;
    }

    public static ListDocWeight getIdDocSumWeightByTermContent(ConnectDB conn, String[] terms, float treshold, int offset, int limit) throws QueryNoResultException {
        //create sql query statement... OK...
        String sql = "SELECT DISTINCT(id_doc), SUM(bobot) FROM docterm WHERE bobot > "+treshold+" AND term IN(\'";
        int i=0;
        while(i<terms.length){
            if(i>0){
                sql = sql + ", \'";
            }
            sql = sql + terms[i] + "\'";
            i++;
        }
        sql = sql + ") GROUP BY id_doc ORDER BY COUNT(term)  DESC, SUM(bobot) DESC LIMIT "+ offset +","+limit+";";

        ArrayList<ArrayList<Object>> tempObject= conn.getAllDatas(sql);
        i=0;
        ListDocWeight listDoc = new ListDocWeight();
        while(i<tempObject.get(0).size()){
            listDoc.add(tempObject.get(0).get(i).toString(), Float.valueOf(tempObject.get(1).get(i).toString()));
            i++;
        }
        return listDoc;
    }

    public static ArrayList<ArrayList<String>> getDocByTermContent(ConnectDB conn, String[] terms, float treshold, int offset, int limit) throws QueryNoResultException{
        ArrayList<ArrayList<String>> listDoc = new ArrayList<ArrayList<String>>();
        String sql ="SELECT  document.ID_DOC, document.TITLE FROM document" +
                " INNER JOIN docterm ON document.ID_DOC = docterm.ID_DOC"+
                " WHERE docterm.bobot > "+treshold+" AND docterm.TERM IN(\'";
        int i=0;
        while(i<terms.length){
            if(i>0){
                sql = sql + ", \'";
            }
            sql = sql + terms[i] + "\'";
            i++;
        }
        sql = sql + ") GROUP BY document.id_doc ORDER BY SUM(bobot) DESC LIMIT "+ offset +","+limit+";";
        
        ArrayList<ArrayList<Object>> obj = conn.getAllDatas(sql);
        ArrayList<String> tmp1 = new ArrayList<String>();
        ArrayList<String> tmp2 = new ArrayList<String>();

        i=0;
        while(i<obj.get(0).size()){
            //System.out.println(obj.get(0).get(i) + "----------" + obj.get(1).get(i));
            tmp1.add(obj.get(0).get(i).toString());
            tmp2.add(obj.get(1).get(i).toString());
            i++;
        }
        listDoc.add(tmp1);
        listDoc.add(tmp2);
        return listDoc;
    }

    public static void insert(ConnectDB conn, int idDoc, String termContent, int frequency){
        String sql = "INSERT INTO docterm (id_doc, term, freq, bobot) values (\'"+idDoc+"\', \'"+termContent+"\', " + frequency+", 0)";
        conn.createStatement(sql);
    }


    public static String getIdDoc(ConnectDB conn, int index) throws QueryNoResultException {
        String sql ="SELECT id_doc, term, freq FROM docterm ORDER BY term ASC LIMIT "+ index +",1;";
        String idDoc = conn.getSingleData(sql, 1).toString();
        return idDoc;
    }

    public static int getTermFrequency(ConnectDB conn, int index) throws QueryNoResultException{
        String sql ="SELECT id_doc, term, freq FROM docterm ORDER BY term ASC LIMIT "+ index +",1;";
        int termFrequency = Integer.valueOf(conn.getSingleData(sql, 3).toString());
        return termFrequency;
    }
    //private static Object getSingleRecord(){}

    public static int getNDocByTermContent(ConnectDB conn, String termContent) throws QueryNoResultException {
        String sql = "SELECT COUNT(*) FROM docterm WHERE term = \'" + termContent + "\'";
        int nDoc = conn.countRowsCustomised(sql);
        return nDoc;
    }

    public static int getNTermDocument(ConnectDB conn) {
        int nTermDoc=0;
        nTermDoc = conn.countRows("docterm");
        return nTermDoc;
    }

    public static String getTermContent(ConnectDB conn, int index) throws QueryNoResultException {
        String sql ="SELECT id_doc, term, freq FROM docterm ORDER BY term ASC LIMIT "+ index +",1;";
        String termContent = conn.getSingleData(sql, 2).toString();
        return termContent;
    }

    public static int getTermFrequency(ConnectDB conn, String idDoc, String termContent) throws QueryNoResultException {
        String sql ="SELECT freq FROM docterm WHERE id_doc = \'"+idDoc+"\' AND term = \'"+termContent+"\'";
        int termFrequency=Integer.valueOf(conn.getSingleData(sql, 1).toString());
        return termFrequency;
    }

    public static float getWeightTermDocument(ConnectDB conn, String termContent, String idDoc) throws QueryNoResultException {
        String sql = "SELECT bobot FROM docterm WHERE id_doc = \'" + idDoc + "\' AND term = \'" + termContent + "\'";
        float weight = Float.valueOf(conn.getSingleData(sql, 1).toString());
        return weight;
    }

    public static boolean isExists(ConnectDB conn, String termContent, String idDoc) throws QueryNoResultException {
        String sql = "SELECT * FROM docterm WHERE id_doc = \'" + idDoc + "\' AND term = \'" + termContent + "\'";
        return conn.isExistInDatabase(sql);
    }

    public static void update(ConnectDB conn, String idDoc, String termContent, double weight) {
        String sql = "UPDATE docterm SET bobot =" + weight +
                "WHERE id_doc = \'" + idDoc +
                "\' AND term = \'" + termContent + "\'";
        conn.createStatement(sql);
    }


}
